package com.dwei.sql;

import java.util.ArrayList;
import java.util.List;

import javax.xml.parsers.ParserConfigurationException;

import org.apache.commons.lang.StringUtils;
import org.dom4j.Document;
import org.dom4j.DocumentFactory;
import org.dom4j.Element;

import com.dwei.Global;

public class SqlHelper {
//============================================================================
//static
//============================================================================
	public static String REG_MULTY = "CONSTRAINT (\\w*_\\w*) ((FOREIGN KEY)|(CHECK)|(PRIMARY KEY)|(UNIQUE))( *)\\([\\w,]+\\)";
	public static String REG_SINGLE = "((FOREIGN KEY)|(CHECK)|(PRIMARY KEY)|(UNIQUE))( *)\\([\\w,]+\\)";
	public static String REG_PRIMARY_KEY = "[ \\w,()]*PRIMARY KEY[ \\w,()]*";
	public static String MULTY_PRIMARY_KEY = "PRIMARY KEY(";
	public static String PROPERTY_SEPARATOR = "[ ()]";
	public static String DEFAULT = "DEFAULT";
	public static String COMMENT = "COMMENT";
//============================================================================
//parameter
//============================================================================
	private Document configuration;
	private Element root;
	private Element common;
	private Element entities;
	/** 分解后的sql建表命令语句集合 */
	private List<String> creates;
//============================================================================
//constructor
//============================================================================
	public SqlHelper(String sql) throws ParserConfigurationException {
		configuration = new DocumentFactory().createDocument(Global.COMMON_ENCODING_DEFAULT);
		setRoot(configuration.addElement(Global.CONF_ROOT));
		setCommon(getRoot().addElement(Global.CONF_COMMON));
		setEntities(getRoot().addElement(Global.CONF_ENTITIES));
		
		common.addElement(Global.COMMON_ENCODING).addText(Global.COMMON_ENCODING_DEFAULT);
		common.addElement(Global.COMMON_SOURCE).addText(Global.COMMON_SOURCE_DEFAULT);
		common.addElement(Global.COMMON_ENTITY).addText(Global.COMMON_ENTITY_DEFAULT);
		common.addElement(Global.COMMON_DAO).addText(Global.COMMON_DAO_DEFAULT);
		common.addElement(Global.COMMON_SERVICE).addText(Global.COMMON_SERVICE_DEFAULT);
		common.addElement(Global.COMMON_SERVICE_IMP).addText(Global.COMMON_SERVICE_IMP_DEFAULT);
		common.addElement(Global.COMMON_MAPPER).addText(Global.COMMON_MAPPER_DEFAULT);
		common.addElement(Global.COMMON_INJECTION).addText(Global.COMMON_INJECTION_DEFAULT);
		
		convertToEntities(sql);
	}
//============================================================================
//private
//============================================================================
	private void convertToEntities(String sql) {
		sql = sql
				.replaceAll("[\\n\\r\\f\\v]+", "")
				.replaceAll("[ \\t]+", " ")
				.replaceAll(" *, *", ",")
				.replaceAll(" *\\) *", "\\)")
				.replaceAll(" *\\( *", "\\(")
				.replaceAll(Global.SQL_SEPARATOR + "+", Global.SQL_SEPARATOR)
				.replaceAll(" *" + Global.SQL_SEPARATOR + " *", Global.SQL_SEPARATOR)
				.toUpperCase();
		int first = sql.indexOf(Global.SQL_CREATE_TABLE);//查找第一条建表语句索引
		if (first == -1) return;//如果未找到建一语句返回
		sql = sql.substring(first);//截取包含建表语句的字符串
		String[] sqls = sql.split(Global.SQL_SEPARATOR);
		String tempSQL;
		int last;
		creates = new ArrayList<String>();
		for (int index = 0;index < sqls.length;index++) {
			tempSQL = sqls[index];
			first = tempSQL.indexOf(Global.SQL_CREATE_TABLE);
			if (first == -1) continue;//判断该SQL命令是否是建表语句
			last = tempSQL.lastIndexOf(Global.SQL_R_BRACKET);//sql语句中的最后一个右括号位置
			if (last == -1 || last < first + 17) continue;//17 = "create table X ( )"
			tempSQL = tempSQL.substring(first, last + 1);
			creates.add(tempSQL);
			//将建表语句转换成Property对象
			convertToEntity(tempSQL);
		}
	}
//============================================================================
//private
//============================================================================
	/**
	 * 
	 * @param sql 大写的建表语句
	 */
	private void convertToEntity(String sql) {
		System.out.println(sql);
		Element entity = getEntities().addElement(Global.ENTITY);//添加一个新的entity节点
		if (!initializeAttribute(entity, sql)) {
			getEntities().remove(entity);
			return;
		}
		if (!initializeProps(entity, sql)) {
			getEntities().remove(entity);
			return;
		}
	}
	
	private boolean initializeProps(Element entity, String sql) {
		int indexLBracket = sql.indexOf(Global.SQL_L_BRACKET);//左括号位置
		int indexRBracket = sql.lastIndexOf(Global.SQL_R_BRACKET);//左括号位置
		if (indexLBracket == -1 || indexLBracket + 2 > indexRBracket) return false;//如果没有属性直接返回
		Element prop;
		sql = sql.replaceAll(",+", ",");
		String[] component = sql.substring(indexLBracket + 1, indexRBracket).split(",");
		entity.addAttribute(Global.ENTITY_KEY, "");
		for (int index = 0;index < component.length;index++) {
			prop = entity.addElement(Global.ENTITY_PROP);
			prop.addAttribute(Global.PROP_JAVADOC, "");
			if (!initializeProp(prop, component[index], entity)) entity.remove(prop);
		}
		//判断是否有为复合主键
		int first = sql.indexOf(MULTY_PRIMARY_KEY);
		if (first != -1) {
			int last = sql.indexOf(Global.SQL_R_BRACKET, first);
			String multyPK = sql.substring(first, last);
			first = multyPK.indexOf(Global.SQL_L_BRACKET);
			if (first != -1 && !multyPK.endsWith(Global.SQL_L_BRACKET)) {
				multyPK = multyPK.substring(first + 1);
				if (multyPK.length() != 0) entity.addAttribute(Global.ENTITY_KEY, multyPK);
			}
		}
		return true;
	}
	
	private boolean initializeProp(Element prop, String sql, Element entity) {
		int first;//第一个左括号位置
		int last;//最后一个右括号位置
		String primaryKey;//主键
		//如果该属性是约束信息(UNIQUE()，FOREIGN KEY()，CHECK())，找到主键
		if (sql.matches(REG_SINGLE) || sql.matches(REG_MULTY)) {
//			if (sql.matches(REG_PRIMARY_KEY)) {
//				first = sql.indexOf(Global.SQL_L_BRACKET);
//				last = sql.lastIndexOf(Global.SQL_R_BRACKET);
//				if (last <= first) return false;
//				primaryKey = sql.substring(first + 1, last);
//				entity.addAttribute(Global.ENTITY_KEY, primaryKey);
//			}
			return false;
		}
		//如果该属性是列定义
		String[] component = sql.split(PROPERTY_SEPARATOR);
		if (component.length < 2) return false;
		//设置表列名
		String column = component[0];
		prop.addAttribute(Global.PROP_COLUMN, column);
		String jdbcType = component[1];
		JavaType javaType = JavaType.getTypeByJdbc(jdbcType);
		if (javaType == null) return false;
		//设置表列类型
		prop.addAttribute(Global.PROP_JDBCTYPE, javaType.getJdbcType());
		//设置java类型
		prop.addAttribute(Global.PROP_JAVATYPE, javaType.getJavaType());
		//设置java属性名
		column = column.replaceAll(Global.TABLE_COLUMN_SEPARATOR + "+", Global.TABLE_COLUMN_SEPARATOR);
		component = column.split(Global.TABLE_COLUMN_SEPARATOR);
		StringBuffer property = new StringBuffer();
		for (int index = Global.TABLE_COLUMN_PRE_UNUSE;index < component.length - Global.TABLE_COLUMN_SUF_UNUSE;index++) {
			property.append(StringUtils.capitalize(component[index].toLowerCase()));
		}
		if (property.length() == 0) return false;
		prop.addAttribute(Global.PROP_PROPERTY, StringUtils.uncapitalize(property.toString()));
		//设置主键
		if (sql.matches(REG_PRIMARY_KEY)) entity.addAttribute(Global.ENTITY_KEY, column);
		//设置默认值
		first = sql.indexOf(DEFAULT);
		String defaultValue;
		if (first != -1) {
			defaultValue = sql.substring(first);
			component = defaultValue.split(" ");
			if (component.length > 1) {
				defaultValue = component[1].replace("\'", "\"");
				defaultValue += javaType.getSuffix();
				prop.addText(defaultValue);
			}
		}
		//设置注释
		first = sql.indexOf(COMMENT);
		if (first != -1) {
			defaultValue = sql.substring(first);
			component = defaultValue.split(" ");
			if (component.length > 1) {
				defaultValue = component[1].replaceAll("[\"\']", "");
				prop.addAttribute(Global.PROP_JAVADOC, defaultValue);
			}
		}
		return true;
	}
	
	private boolean initializeAttribute(Element entity, String sql) {
		int indexLBracket = sql.indexOf(Global.SQL_L_BRACKET);//左括号位置
		if (indexLBracket < 15) return false;//15 = "CREATE TABLE X ("
		//设置表名属性 Global.ENTITY_TABLE
		String command = sql.substring(0, indexLBracket);//建表语句的指令字符串，"CREATE TABLE X"
		String[] component = command.split(" ");//将command以空格分隔成字符串数组，第3个字符串即为表名
		if (component.length < 3) return false;//如果component长度小于3，即建表命令无效
		String tableName = component[2];//表名
		entity.addAttribute(Global.ENTITY_TABLE, tableName);//设置表名
		//设置类名属性Global.ENTITY_NAME
		tableName = tableName.replaceAll(Global.TABLE_NAME_SEPARATOR + "+", Global.TABLE_NAME_SEPARATOR);
		component = tableName.split(Global.TABLE_NAME_SEPARATOR);//表名以Global.TABLE_NAME_SEPARATOR分隔为字符串数组，用来组装成java对象名
		StringBuffer entityName = new StringBuffer();
		for (int index = Global.TABLE_NAME_PRE_UNUSE;index < component.length - Global.TABLE_NAME_SUF_UNUSE;index++) {
			entityName.append(StringUtils.capitalize(component[index].toLowerCase()));
		}
		if (entityName.length() == 0) return false;//如果组装的表对应的类名为空，创建无效
		entity.addAttribute(Global.ENTITY_NAME, entityName.toString());
		//设置sql mapper配置属性
		entity.addAttribute(Global.ENTITY_INSERT, Global.BOOLEAN_TRUE);
		entity.addAttribute(Global.ENTITY_UPDATE, Global.BOOLEAN_TRUE);
		entity.addAttribute(Global.ENTITY_DELETE, Global.BOOLEAN_TRUE);
		return true;
	}
//============================================================================
//getter/setter
//============================================================================
	public Document getConfiguration() {
		return configuration;
	}
	public void setConfiguration(Document configuration) {
		this.configuration = configuration;
	}
	public Element getRoot() {
		return root;
	}
	public void setRoot(Element root) {
		this.root = root;
	}
	public Element getCommon() {
		return common;
	}
	public void setCommon(Element common) {
		this.common = common;
	}
	public Element getEntities() {
		return entities;
	}
	public void setEntities(Element entities) {
		this.entities = entities;
	}
	public List<String> getCreates() {
		return creates;
	}
	public void setCreates(List<String> creates) {
		this.creates = creates;
	}
}
